This article details three methods of handling duplicate records. The first method prints labels or form letter layouts without printing duplicates. The second method requires more setup than the first, but allowexporting a list without duplicates for mail merge in a word processor. Neither method deletes the duplicate records from your file. The third method automates deleting duplicates from your database.
The first two methods, which do not delete duplicate records from your file, are explained for two reasons. First, they are faster and easier than the third method. Thus, they are good to use when you need to print labels or form letters without duplicates, but current deadlines don't allow time to delete the duplicates. Second, your database needs may require you to maintain duplicate records. For example, consider a doctor's patient list, where each record contains a patient's name. The database contains non-duplicated names, but contains duplicated addresses (e.g., several members of a family). The doctor wants to mail only one letter to each address. In this case, database needs dictate that the file contain duplicates (addresses), so either of the first two methods described below could be used to prevent duplicate mailings.
Your data entry must be consistent and complete for these methods to work accurately. FileMaker cannot detect duplicate records when the data is not entered consistently. For example, a person entered into two records as Robert Smith and Bob Smith may not be detected as a duplicate. Likewise, this method may falsely consider records as duplicates if there is insufficient data in your file to differentiate records. For example, if your database contains two Smiths in the same zip code, and you're only using last name and zip code to detect duplicates, the records will appear as duplicates to FileMaker.
Defining the Duplicate Checking Field
To check for duplicate records (e.g., names or addresses), the database must have a single field that contains enough information to uniquely identify each person or address. Obviously, a field containing the last name, or even the full name is usually not adequate; the address field is too prone to inconsistent data entry to be a good choice. However, a field containing the phone number or social security number would be fine. If your database does not contain such a field, you can create a calculation field that contains enough information from other fields to uniquely identify each person. To create this field, first decide if you want to check for duplicate names or duplicate addresses. Then pick one of the choices A-E from the list below.
Duplicate Check Field (choose one)
* Check for duplicate names:
A. Social Security number
B. Combination of phone number and first name
C. Combination of last name, first name, ZIP code and street address number
* Check for duplicate addresses:
D. Phone number
E. Combination of last name, ZIP code and street address number
If you've chosen A or D from the list above, use this existing database field in the following instructions wherever they refer to the Duplicate Check field. If you've chosen B, C, or E, define this new field as your Duplicate Check field: Go to the Select menu and choose Define Fields. Type the name Duplicate Check for the field name; click Calculation for the field type, then click Create. In the formula window, enter the appropriate formula from List 2 below:
List 2 - Duplicate Check Field Formula
Choice Formula
B Phone Number & " " & First Name
C Last Name & " " & First Name & " " & ZIP code & " " &
Left (Address,position (Address," ",1)-1)
E Last Name & " " & ZIP Code & " " & left (Address,
position (Address," ",1) - 1)
(You should type a space between the quotes in the formulas above. Whichever formula you enter from above, set the calculation to result in Text.)
Printing Mailing Labels while Omitting Duplicates
Eliminating duplicates from mailing label printouts involves two steps: defining the duplicate checking field, then defining a label layout. The label layout is similar to a normal label layout, except the fields are in a sub-summary part instead of a body part.
Eliminating duplicates from mailing label printouts involves two steps: defining the duplicate checking field, then defining a label layout. The label layout is similar to a normal label layout, except the fields are in a sub-summary part instead of a body part.
Instructions in Brief...
Make a standard label layout. Or, to print form letters within FileMaker Pro, create a form letter layout. (The following instructions explain a label layout). Change the body section to a sub-summary part when sorted by the Duplicate Check field. If you're printing 2 or 3-across labels, set the column setup for "Down First." Finally, sort by the sub-summary field and print.
Instructions in Detail...
Step #1. Defining the Duplicate Check field.
Follow the instructions in the previous section, "Defining the Duplicate Checking Field"
Step #2. Create a standard label layout (or, if you already have a label layout, you can duplicate this layout, then proceed to step #3):
- Choose Layout from the Select menu.
- Choose New Layout from the Edit menu
- Click Label Layout, then click OK. The label setup window will appear. Set your label size then click OK. The field list window will appear. Double-click on each field you want on your label, then click OK. A standard label layout should now be created.
- {Skip this step if you're running FileMaker Pro 2.0 and you've chosen a specific label type (e.g., Avery 4143) in the previous step} If printing on label sheets, adjust the Header to the correct height. The header should equal the gap between the top of the label sheet and the top of the first row of labels.
Step #3. Changing the Body to a sub-summary part:
- Choose Define Parts from the Layout menu. Click on Body, then click Change. The part definition window will appear. "Click Sub-summary when sorted by:" In the field list, click on the Duplicate Check field. Click OK. In the next dialog box, click "Print Above."
- If you're printing 2 or 3-across labels, choose Layout Options from the Layout menu; click Down First, then click OK. Your Layout is now complete.
Step #4.
Sort by the sub-summary field (Duplicate Check), then print. You'll notice that the Duplicate Check field is not placed on the layout. We have created this field only to make the sub-summary part.
For future printing, skip steps #1 and #2; just switch to this layout, sort and print.
Exporting for Mail Merge while Omitting Duplicates
The following will omit duplicates from the records being browsed. You then can print form letters (mail merge) without duplicates.
Step #1. Define the five fields in the list below. The field called Duplicate Check is the field you've defined in the previous section, "Defining the Duplicate Checking Field".
Counter1 (number)
Counter2 (Calculation, number result) = Counter1 + 1
Duplicate Check {this is the field you've chosen from List 1}
Duplicate Lookup (text, Lookup: Same file)
Copy contents of Duplicate Check into Duplicate Lookup when Counter2 matches a new entry in Counter1
- Go to the first record. Click into the Counter1 field.
- Select Replace from the Edit menu
- Click Replace with Serial Numbers, then click Replace. Proceed to step #3.
{FileMaker Pro 1.0}
- Sort the database by the Duplicate Check field
- Using a spreadsheet program, create a series of numbers in a single column equal to the number of records in your database. That is, if your database has 1000 records, make a series from 1 to 1000 in increments of one. Save As a text file.
- Import the text file into your database: Choose Import from the File menu; select the text file; in the Import Data dialog box, align the Counter1 field with the data in the text file; turn this field ON for import; turn all other fields OFF for import; choose Replace Data in Current Found Set; click OK.
- Click into the Counter1 field. Select Relookup from the Edit menu. Click OK.
* When done, the Compare field will contain the word 'duplicate' for each extra duplicate record and 'single' for each non-duplicate record and the first occurrence of each duplicate. Browse your database to verify that this worked.
Step #3. Choose the Find command from the Select menu. Type "single" (no quotes) into the Compare field, then click the Find button.
Step #4. Exporting the data
- Choose Export or Export Records from the File menu
- Type a name for the export file, choose Merge file for the file type, then click New.
For future exporting of non-duplicate data, just do steps #2 through #4.
Deleting Duplicates in Bulk
The following will delete duplicates automatically from your mailing list.
*** Since we will be deleting records, first make a current backup copy of your database. Use either the Save a Copy command or duplicate the file in the Finder. You will then have a current backup of your file, should you need it later.
Steps #1 - #2. Do steps #1 through #2 described in the previous section, "Exporting for Mail Merge while Omitting Duplicates."
Step #3. Choose the Find command from the Select menu. Type "duplicate" (no quotes) into the Compare field, then click the Find button.
Step #4. Select Delete Found Set from the Edit menu. Click OK to accept the delete. Your extra duplicate records will now be deleted.
For future deleting of non-duplicate data, just do steps #2 through #4.
Copyright (C)1990 Claris Corporation. All rights reserved.
Claris is a registered trademark of Claris Corporation. All other Claris trademarks and registered trademarks are the property of Claris Corporation. All third party ademarks or registered trademarks are the property of their respective owners. If you have a fax machine, try our Fax AnswerLine for answers 24 hours a day. Call (800) 800-8954.
